﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;
using NTQOnlineExam.Services.SearchCriteria;

namespace NTQOnlineExam.Services.DBCommand.Account
{
    public class GetUserProfileDtoBySearchCriteriaDbCommand : ICommand<SearchResult<UserProfileDTO>>
    {
        private readonly int _pageSize;
        private readonly int _pageIndex;
        private static SearchCriteria.SearchCriteria _criteria;

        private const string SQL = @"
              WITH PAGING AS (
                SELECT u.UserId, u.Username, u.FirstName, u.LastName, u.Email, row_number() over (order by {0}) as rownumber
                FROM dbo.UserProfile u
               WHERE IsDeleted = 0 {1})
        ";

        public GetUserProfileDtoBySearchCriteriaDbCommand(SearchCriteria.SearchCriteria criteria, int pageSize, int pageIndex)
        {
            _pageSize = pageSize;
            _pageIndex = pageIndex;
            _criteria = criteria;
        }

        public SearchResult<UserProfileDTO> Execute()
        {
            var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            using (var connection = new SqlConnection(connectionString))
            {
                IList<UserProfileDTO> items = new List<UserProfileDTO>();
                int totalCount = 0;
                var cmd = new SqlCommand(BuildQuery(), connection);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
                cmd.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = _criteria.Keyword;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    totalCount = reader.GetInt32(0);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        items.Add(GetDto(reader)); 
                    }
                }
                finally
                {
                    reader.Close();
                }
                return new SearchResult<UserProfileDTO>(items, totalCount);
            }
        }
        private UserProfileDTO GetDto(SqlDataReader reader)
        {
            return new UserProfileDTO
            {
                UserId = (int)reader["UserId"],
                UserName = reader["UserName"].ToString(),
                FirstName = reader["FirstName"] != DBNull.Value ? reader["FirstName"].ToString() : string.Empty,
                LastName = reader["LastName"] != DBNull.Value ? reader["LastName"].ToString() : string.Empty,
                Email = reader["Email"] != DBNull.Value ? reader["Email"].ToString() : string.Empty,

            };
        }

        private string BuildQuery()
        {
            var query = new StringBuilder();
            var sqlQuery = string.Format(SQL, BuildOrderClause(), BuildWhereClause());
            query.AppendFormat("{0} {1};{0} {2}", sqlQuery, @"SELECT COUNT(1) FROM paging", @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
            return query.ToString();
        }

        private string BuildWhereClause()
        {
            string whereClause = string.Empty;
            if (!string.IsNullOrEmpty(_criteria.Keyword))
                whereClause = @" AND (Username LIKE '%' + @Keyword +'%' 
                                    OR FirstName LIKE '%'+ @Keyword +'%' 
                                    OR LastName LIKE '%'+ @Keyword +'%' 
                                    OR Email LIKE '%'+ @Keyword +'%' 
                                    OR (FirstName + ' ' + LastName) LIKE '%'+ @Keyword +'%' 
                                  )
                                ";

            if (!string.IsNullOrEmpty(whereClause))
                return whereClause;
            return string.Empty;
        }

        private static string BuildOrderClause()
        {
            StringBuilder orderClause = new StringBuilder();
            foreach (SearchSortItem sortItem in _criteria.SortItems)
            {
                orderClause.AppendFormat("u.{0} {1}", sortItem.SortExpression, sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                orderClause.Append(',');
            }

            if (orderClause.Length <= 0)
            {
                orderClause.Append("FirstName, LastName ASC");
            }
            else
            {
                orderClause.Remove(orderClause.Length - 1, 1);
            }
            return orderClause.ToString();
        }
    }
}
